# _*_ coding: utf-8 _*_
from odoo import api, fields, models, tools


class StockInoutDetial(models.Model):
    _name = "stock.inout.detail"
    _auto = False
    _order = 'create_date asc'

    move_id = fields.Many2one('stock.move', u'Stock Move', required=True)
    # location_id = fields.Many2one('stock.location', 'Location', required=True)
    product_id = fields.Many2one('product.product', '产品', required=True)
    quantity = fields.Float('数量')
    create_date = fields.Datetime('创建日期')
    serial_number = fields.Char('追踪号', required=True)
    origin = fields.Char(u'源单据', required=True)
    # picking_type_id = fields.Many2one('stock.picking.type',
    #                                   string=u'拣货类型')
    picking_type_id = fields.Char(string=u'拣货类型')
    lot_id = fields.Many2one('stock.production.lot', string='追踪号/批次号')
    product_uom_id = fields.Many2one(
        'product.uom', string='单位', related='product_id.uom_id',
        readonly=True)
    force_date = fields.Datetime(u'强制时间')

    @api.model_cr
    def init(self):
        tools.drop_view_if_exists(self._cr, 'stock_inout_detail')
        self._cr.execute("""
                CREATE VIEW stock_inout_detail AS (
                  (SELECT sq.id as id,
                    spl.id AS lot_id,
                    sqmr.move_id AS move_id,
                    sm.product_id AS product_id,
                    sm.create_date AS create_date,
                    sm.origin AS origin,
                    spt.name AS picking_type_id,
                    sm.force_date,
                    sq.ref AS serial_number,
                    sq.qty AS quantity
                    FROM stock_quant_move_rel sqmr
                    JOIN stock_move sm ON sm.id=sqmr.move_id
                    JOIN stock_quant sq ON sq.id=sqmr.quant_id
                    LEFT JOIN stock_picking_type spt on sm.picking_type_id=spt.id
                    JOIN stock_production_lot spl ON spl.id=sq.lot_id WHERE picking_id is not null)
                UNION ALL
                (SELECT sq.id as id,
                    spl.id AS lot_id,
                    sqmr.move_id AS move_id,
                    sm.product_id AS product_id,
                    sm.create_date AS create_date,
                    sm.origin AS origin,
                    spt.name AS picking_type_id,
                    sm.force_date,
                    sq.ref AS serial_number,
                    sq.qty AS quantity
                    FROM stock_quant_move_rel sqmr
                    JOIN stock_move sm ON sm.id=sqmr.move_id
                    JOIN stock_quant sq ON sq.id=sqmr.quant_id
                    JOIN mrp_production mp on mp.id = sm.production_id
                    LEFT JOIN stock_picking_type spt on mp.picking_type_id=spt.id
                    JOIN stock_production_lot spl ON spl.id=sq.lot_id WHERE picking_id is null and production_id is not null)
                UNION ALL
                (SELECT sq.id as id,
                    spl.id AS lot_id,
                    sqmr.move_id AS move_id,
                    sm.product_id AS product_id,
                    sm.create_date AS create_date,
                    sm.origin AS origin,
                    spt.name AS picking_type_id,
                    sm.force_date,
                    sq.ref AS serial_number,
                    sq.qty AS quantity
                    FROM stock_quant_move_rel sqmr
                    JOIN stock_move sm ON sm.id=sqmr.move_id
                    JOIN stock_quant sq ON sq.id=sqmr.quant_id
                    JOIN mrp_production mp on mp.id = sm.raw_material_production_id
                    LEFT JOIN stock_picking_type spt on mp.picking_type_id=spt.id
                    JOIN stock_production_lot spl ON spl.id=sq.lot_id WHERE raw_material_production_id is not null)
                UNION ALL
                (SELECT sq.id as id,
                    spl.id AS lot_id,
                    sqmr.move_id AS move_id,
                    sm.product_id AS product_id,
                    sm.create_date AS create_date,
                    sm.origin AS origin,
                    COALESCE(SPT.NAME,'盘点') AS picking_type_id,
                    sm.force_date,
                    sq.ref AS serial_number,
                    sq.qty AS quantity
                    FROM stock_quant_move_rel sqmr
                    JOIN stock_move sm ON sm.id=sqmr.move_id
                    JOIN stock_quant sq ON sq.id=sqmr.quant_id
                    JOIN stock_inventory si on si.id=sm.inventory_id
                    LEFT JOIN stock_picking_type spt on sm.picking_type_id=spt.id
                    JOIN stock_production_lot spl ON spl.id=sq.lot_id WHERE inventory_id is not null)
                )""")
